package www.lagou.app;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;
import www.lagou.entity.Phone;
import www.lagou.utils.DruidUtils;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.List;

public class PhoneTest {

    /**
     * 需求1:  查询价格高于2000元，生产日期是2019年之前的所有手机
     */
    @Test
    public void test1(){
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //编写sql语句
            String sql = "SELECT * FROM phone WHERE prodate < '2019-01-01' AND price > 2000";
            con = DruidUtils.getConnection();
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getInt("id")+"\t"+rs.getString("pname")+"\t"+rs.getDouble("price")+"\t"+rs.getDate("prodate")+"\t"+rs.getString("color"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DruidUtils.close(con,st,rs);
        }
    }

    /**
     * 需求2:  查询所有颜色是白色的手机信息
     */
    @Test
    public void test2(){
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //获取连接
            con = DruidUtils.getConnection();
            //编写SQL语句
            String sql = "SELECT * FROM phone WHERE color = '白色'";
            st = con.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getInt("id")+"\t"+rs.getString("pname")+"\t"+rs.getDouble("price")+"\t"+rs.getDate("prodate")+"\t"+rs.getString("color"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            DruidUtils.close(con,st,rs);
        }
    }
}
